# -- coding cp949 --
#-*- encoding: utf-8 -*-
import sys
sys.path.append(r'Y:\Lib\site-packages\32')
import MySQLdb
import string
import random
import time


from lib.papyDB import papyDB
from lib import globalVar as GV
papyDB = papyDB()


class bulkData():    
    def __init__( self ):        
        self.db = MySQLdb.connect( host=GV.DATABASE_HOST , user='root' , passwd='studio77' , db='FC' , charset='utf8' )
        self.cr = self.db.cursor()
      
    def closeEvent( self , event ) :        
        papyDB.cr.close()        
        
    def exct( self , query ):
        self.cr.execute( query )
        self.db.commit()
       
    def dataCount( self , table , where = '' , val ='' ):
        query = "SELECT count(*) FROM %s" % table
        if where!='':
            query += " WHERE %s = %s" % (where,val)
        #print query
        self.exct(query)
        return int( self.cr.fetchone()[0] )
    
    def pkCheck( self , table , att1 , att2 , val1 , val2 ):
        query = "SELECT COUNT(*) FROM %s WHERE %s=%d AND %s=%d" % (table,att1,val1,att2,val2)
        self.exct(query)
        res = self.cr.fetchone()[0] 
        return res        
    
    def randDate( self ):
        now = time.time()
        gap = random.randrange(-35000000,35000000)
        time1 = int(now-44800000)
        time2 = int(now+44800000)
        strt_time = time.localtime( random.randrange(time1,time2) )
        end_time = time.localtime( random.randrange(time1,time2) )
        fin_time = time.localtime( random.randrange(time1,time2)+gap )
        while strt_time >  end_time :
            end_time = time.localtime( random.randrange(time1,time2) )    
        while strt_time > fin_time :
            fin_time = time.localtime( random.randrange(time1,time2) )
        if fin_time > time.localtime(now) :
            fin_time = None        
        a = lambda x : str(x.tm_year )+'-'+str(x.tm_mon ).zfill(2)+'-'+str(x.tm_mday ).zfill(2)
        startDate = a( strt_time )
        endDate = a( end_time )
        finalDate = a( fin_time ) if fin_time != None else '0000-00-00' 
#        print 'fin_time : ' , finalDate           
        return startDate , endDate , finalDate    
     
    def wordList( self ):
        wordFile = open(r"word.txt", 'r')
#        wordFile = open(r"D:\WORK\kali\General\papyrus\test\word.txt", 'r')
        wordList = []
        for line in wordFile:
            line = line[0].upper() + line[1:-1]
            wordList.append(line)
        wordFile.close()
        return wordList
    
    def kwordList( self ):
        kwordFile = open(r"kword.txt", 'r')
#        kwordFile = open(r"D:\WORK\kali\General\papyrus\test\kword.txt", 'r')
        kwordList = []
        for line in kwordFile:
            line = line[0].upper() + line[1:-1]
            kwordList.append(line)
        kwordFile.close()
        return kwordList    
       
    def RESET( self , table ):
        deleteQ = "DELETE FROM %s" % table
        AISetQ = "ALTER TABLE %s auto_increment=1" % table
        self.exct(deleteQ)
        self.exct(AISetQ)     
        print table, " : RESET" 
                
    def getFetch(self , query):
        self.cr.execute(query )
        data = self.cr.fetchall() 
        return data[0][0]
             
    def getPrjID (self , seqID):
        prjID = self.getFetch('select prjID from SEQUENCES where seqID="%d"' % seqID)
        return prjID
    
    
    
    
        
    def basicData( self ):
        papyDB.regTable('DEPARTMENT' , 'TD')
        papyDB.regTable('DEPARTMENT' , 'Modeling')
        papyDB.regTable('DEPARTMENT' , 'Animation')
        papyDB.regTable('DEPARTMENT' , 'Rigging')
        papyDB.regTable('DEPARTMENT' , 'Texture')
        papyDB.regTable('DEPARTMENT' , 'Render')
        papyDB.regTable('DEPARTMENT' , 'FX')
        papyDB.regTable('DEPARTMENT' , 'Comp')
        papyDB.regTable('DEPARTMENT' , 'Lighting')
        papyDB.regTable('DEPARTMENT' , 'Layout')
                
        papyDB.regTable('USER_MACHINE' , 'karas' , '192.168.131.249' ) 
        papyDB.regTable('USER_MACHINE' , 'optimus' , '192.168.131.201' )
        papyDB.regTable('USER_MACHINE' , 'calcium' , '192.168.131.001' )    
        papyDB.regTable('USER_MACHINE' , 'magnecium' , '192.168.131.002' )
        papyDB.regTable('USER_MACHINE' , 'oxygen' , '192.168.131.003' )        
        papyDB.regTable('USER_MACHINE' , 'sdfa' , '192.168.131.004' )
        papyDB.regTable('USER_MACHINE' , 'asfsf' , '192.168.131.006' )
        papyDB.regTable('USER_MACHINE' , 'fwee' , '192.168.131.007' )
        papyDB.regTable('USER_MACHINE' , 'sdfsdf' , '192.168.131.008' )
        papyDB.regTable('USER_MACHINE' , 'werws' , '192.168.131.009' )
#        
#        papyDB.regTable('USER_INFO' , '오호준' , 'onetera' , 'studio77' , 'onetera@gmail.com','010-0000-0000' , 1 , 1,1 )
        papyDB.regTable('USER_INFO' , '조수진' , 'sujin' , 'sujin' , 'test@gmail.com','010-0000-0000' , 2 , 2, 1)
        papyDB.regTable('USER_INFO' , '이모델' , 'modeling' , '1' , 'test@gmail.com','010-0000-0000' , 3 , 3,2 )
        papyDB.regTable('USER_INFO' , '최애니' , 'anim' , '1' , 'test@gmail.com','010-0000-0000' , 3 , 4, 3)
        papyDB.regTable('USER_INFO' , '엄리깅' , 'rigging' , '1' , 'test@gmail.com','010-0000-0000' , 3 , 5, 4)
        papyDB.regTable('USER_INFO' , '강텍스' , 'texture' , '1' , 'test@gmail.com','010-0000-0000' , 3 , 6, 5)
        papyDB.regTable('USER_INFO' , '임렌더' , 'render' , '1' , 'test@gmail.com','010-0000-0000' , 3 , 7, 6)
        papyDB.regTable('USER_INFO' , '박효과' , 'FX' , '1' , 'test@gmail.com','010-0000-0000' , 3 , 8, 7)
        papyDB.regTable('USER_INFO' , '진컴프' , 'comp' , '1' , 'test@gmail.com','010-0000-0000' , 3 , 9, 8)
        papyDB.regTable('USER_INFO' , '김조명' , 'lighting' , '1' , 'test@gmail.com','010-0000-0000' , 3 , 10, 9)
        papyDB.regTable('USER_INFO' , '윤레이' , 'layout' , '1' , 'test@gmail.com','010-0000-0000' , 3 , 1, 10)
  




#        papyDB.regTable('PROJECT' , 'captin ohho' , 'cap' ,1 , 1,'첫번째테스트', '')
#        papyDB.regTable('PROJECT' , 'BANANA' , 'BN' ,1 , 1,'바나나', '')
#        papyDB.regTable('PROJECT' , 'testPrj' , 'tp' ,2 , 2,'프로젝트', '')
#        papyDB.regTable('PROJECT' , 'Disney' , 'mini' ,3 , 3,'', '')
#        papyDB.regTable('PROJECT' , 'Pooh' , 'bear' ,4 , 4,'곰돌이 푸우', '')
#        papyDB.regTable('PROJECT' , 'MISSION IMPOSSIBLE' , 'tom' ,5 , 5,'톰크루즈', '')    
        
        print 'Complete basic data' 
           
    def insertMachine( self , count ):
        nameList = []
        for x in range( count ):
            name = ''
            for n in range( 3 , random.choice(range(5,9)) ):
                name += string.lowercase[random.randrange(0,25)]
            nameList.append(name)
        nameList.sort()
        random.shuffle(nameList)        
        ipList = [ '192.168.131.%s' % str(x) for x in range(255) ]
        random.shuffle(ipList)   
        for i, x in enumerate( nameList ): 
#            dic = {'name':nameList[i] , 'ip':ipList[i] }    
            print nameList[i] , ipList[i]
            papyDB.regTable('user_machine' , nameList[i+1] , ipList[i+1] ) 
#            self.exct( "INSERT INTO user_machine(mName,ipAddress) VALUES('%(name)s','%(ip)s')" % dic )
            
            
            
            
#            if bulkData.dataCount('USER_MACHINE', 'ipAddress', '%s' % dic['ip']) == 0:                
#                query = "INSERT INTO USER_MACHINE(mName,ipAddress) VALUES('%(name)s','%(ip)s')" % dic
#                print query
#                self.exct(query)
#            self.exct( "INSERT INTO USER_MACHINE(mName,ipAddress) VALUES('%(name)s','%(ip)s')" % dic )      
        print 'Complete machine data'    
                       
            
    def insertUser( self , count ):
        for x in range( count ):
            userName=''
            firstName = ['김', '이', '박', '최', '정', '강', '조', '윤', '장', '임', '한', '신', '오', '서', '권']
            name1 = ['수', '호', '선', '민', '지', '효', '진', '준', '인', '지', '윤', '정', '경', '은', '나']
            name2 = ['진', '준', '영', '지', '영', '정', '명', '기', '수', '민', '한', '신', '현', '서', '오']
            userName = firstName[random.randrange(0,15)] + name1[random.randrange(0,15)] + name2[random.randrange(0,15)]         
            
            loginName = ''
            for n in range( 8 ):
                loginName += string.lowercase[random.randrange(0,25)]
                
            userEmail = loginName + '@gmail.com'
            userLevel = random.randrange(2,6) 
            mID = str(x+6)              
            deptID = random.randrange(1,11)    
            
            dic = {'userName':userName , 'loginName':loginName , 'userEmail':userEmail , 'userLevel':userLevel , 'mID':mID , 'deptID':deptID }
            
            
            papyDB.regTable('user_info' , userName , loginName , '1' , userEmail , '010-0000-0000' , userLevel , mID , deptID) 
            
            
            
#            self.exct( """
#                      INSERT INTO USER_INFO(userName,userLogInName,userPW,userEmail,userPhone,userLevel,mId,deptID) 
#                      VALUES('%(userName)s','%(loginName)s','1','%(userEmail)s','010-0000-0000','%(userLevel)s','%(mID)s','%(deptID)s')""" % dic )
            
        print 'Complete user data'
            
    def insertAsset( self , count ):
        wordList = self.wordList()                  
        for x in range( count ):
            assetName = wordList[random.randrange(0,846)] + wordList[random.randrange(0,846)]   
                     
            status = ['Ready' , 'WIP' , 'Final' , 'Hold']
            assetStatus = status[random.randrange(0,4)]            
            
            type = ['Character' , 'Vehicle' , 'Prop' , 'Environment']
            assetType = type[random.randrange(0,4)]
                                             
            randDate = bulkData.randDate()
            
            assetDesc = wordList[random.randrange(0,846)] + ' ' + wordList[random.randrange(0,846)]
            
            dic = {'assetName':assetName , 'assetStatus':assetStatus , 'assetStartDate':randDate[0] , 'assetEndDate':randDate[1] ,
                   'assetType':assetType , 'assetFinalDate':randDate[2], 'assetDesc':assetDesc}
            
            self.exct( """
                      INSERT INTO ASSET(assetName,assetStatus,assetStartDate,assetEndDate,assetType,assetFinalDate,assetDesc) 
                      VALUES('%(assetName)s','%(assetStatus)s','%(assetStartDate)s','%(assetEndDate)s','%(assetType)s','%(assetFinalDate)s','%(assetDesc)s')""" % dic )            
            
        print 'Complete asset data'
            
    def insertSeq( self , count ):
        prjCnt = self.dataCount('PROJECT')
        wordList = self.wordList()
        for x in range( count ):
            seqName = wordList[random.randrange(0,846)]
            prjID = random.randrange(1,prjCnt+1)
            seqDesc = wordList[random.randrange(0,846)] + ' ' + wordList[random.randrange(0,846)]
            dic = {'seqName':seqName , 'prjID':prjID , 'seqDesc':seqDesc}    
#            print """INSERT INTO SEQUENCES(seqName,prjID,seqDesc) VALUES('%(seqName)s','%(prjID)s','%(seqDesc)s')""" % dic
            self.exct("""INSERT INTO SEQUENCES(prjID,seqName,seqDesc) VALUES('%(prjID)s','%(seqName)s','%(seqDesc)s')""" % dic)
        print 'Complete sequence data'
                       
    def insertShot( self , count ):
        seqCnt = self.dataCount('SEQUENCES')
        wordList = self.wordList()
        for seqID in range( 1 , seqCnt+1 ) :
            for x in range( count ):
                shotLength = random.randrange(5,100)
                shotName = 'sh' + str(random.randrange(0,999)).zfill(3)
                randDate = bulkData.randDate()
                shotStatus = random.randrange(1,5)
                shotDesc = wordList[random.randrange(0,846)] + ' ' + wordList[random.randrange(0,846)]   
                dic = {'seqID':seqID , 'shotLength':shotLength , 'shotName':shotName , 'shotStartDate':randDate[0] , 'shotEndDate':randDate[1] ,
                       'shotFinalDate':randDate[2] , 'shotStatus':shotStatus , 'shotDesc':shotDesc}
                self.exct("""INSERT INTO SHOT(seqID,shotName,shotLength,shotStartDate,shotEndDate,shotFinalDate,shotStatus,shotDesc)
                        VALUES('%(seqID)s','%(shotName)s','%(shotLength)s','%(shotStartDate)s','%(shotEndDate)s',
                        '%(shotFinalDate)s','%(shotStatus)s','%(shotDesc)s')""" % dic)
        print 'Complete shot data'
        
    def ASSET_TAG( self ):
        wordList = self.wordList()        
        for x in range( len(wordList) ):
            if x % 16 == 0:
                self.exct("INSERT INTO ASSET_TAG(Tag) VALUES('%s')" % wordList[x]) 
#        kwordList = self.kwordList()
#        for y in range( len(kwordList) ):
#            self.exct("INSERT INTO ASSET_TAG(Tag) VALUES('%s')" % kwordList[y])
        print 'Complete tag data'
        
    def PROJECT_has_ASSET ( self , count ):
        prjCnt = self.dataCount('PROJECT')
        assetCnt = self.dataCount('ASSET')
        for x in range( count ):
            prjID = random.randrange(1,prjCnt+1)
            assetID = random.randrange(1,assetCnt+1)
            dic = {'prjID':prjID , 'assetID':assetID}
            if self.pkCheck('PROJECT_has_ASSET', 'prjID', 'assetID', prjID, assetID) == 0:
                self.exct("INSERT INTO PROJECT_has_ASSET(prjID,assetID) VALUES(%(prjID)d,%(assetID)d)" % dic)
            #query = "INSERT INTO PROJECT_has_ASSET(prjID,assetID) VALUES(%(prjID)d,%(assetID)d)" % dic
            #print query   
        print 'Complete project has asset data'  
            
    def SEQUENCES_has_ASSET ( self , count ):
        seqCnt = self.dataCount('SEQUENCES')
        assetCnt = self.dataCount('ASSET')      
        for x in range( count ):
            seqID = random.randrange(1,seqCnt+1)
            assetID = random.randrange(1,assetCnt+1)
            prjID = bulkData.getPrjID(seqID)
            dic = {'prjID':prjID , 'seqID':seqID , 'assetID':assetID}
            if self.pkCheck('SEQUENCES_has_ASSET', 'seqID', 'assetID', seqID, assetID) == 0:
                self.exct("INSERT INTO SEQUENCES_has_ASSET(seqID,assetID) VALUES(%(seqID)d,%(assetID)d)" % dic)
                if self.pkCheck('PROJECT_has_ASSET', 'prjID', 'assetID', prjID, assetID) == 0:
                    self.exct("INSERT INTO PROJECT_has_ASSET(prjID,assetID) VALUES(%(prjID)d,%(assetID)d)" % dic)
                #print "INSERT INTO SEQUENCES_has_ASSET(seqID,assetID) VALUES(%(seqID)d,%(assetID)d)" % dic
        print 'Complete sequences has asset data' 
            
    def SHOT_has_ASSET ( self , count ):
        shotCnt = self.dataCount('SHOT')
        assetCnt = self.dataCount('ASSET')     
        for x in range( count ):
            shotID = random.randrange(1,shotCnt+1)
            assetID = random.randrange(1,assetCnt+1)
            dic = {'shotID':shotID , 'assetID':assetID}
            if self.pkCheck('SHOT_has_ASSET', 'shotID', 'assetID', shotID, assetID) == 0:
                self.exct("INSERT INTO SHOT_has_ASSET(shotID,assetID) VALUES(%(shotID)d,%(assetID)d)" % dic) 
        print 'Complete shot has asset data'   
                        
    def ASSET_VERSION( self ):
        assetCnt = self.dataCount('ASSET')
        for x in range( 1 , assetCnt+1 ):
            for y in range(1 , random.choice(range(2,12))) :
#                print "INSERT INTO ASSET_VERSION(assetID,assetVer) VALUES(%d,%d)" % (x,y)
                self.exct("INSERT INTO ASSET_VERSION(assetID,assetVer) VALUES(%d,%d)" % (x,y))
        print 'Complete asset version data'  

    def ASSET_has_ASSET_TAG( self ):
        assetCnt = self.dataCount('ASSET')
        tagCnt = self.dataCount('ASSET_TAG')
        for x in range( 1 , assetCnt+1 ):
            for y in range(1 , random.choice(range(3,7))) :
                z = random.choice(range(tagCnt))+1
                if self.pkCheck('ASSET_has_ASSET_TAG', 'assetID', 'assetTagID', x, z) == 0:
#                    print x, z
                    self.exct("INSERT INTO ASSET_has_ASSET_TAG(assetID,assetTagID) VALUES(%d,%d)" % (x,z))
        print 'Complete asset has tag data' 
        
        
    def ASSET_JOB( self , count ):
        statusList = ['Ready' , 'WIP' , 'Final' , 'Hold']
        wordList = self.wordList() 
        assetCnt = self.dataCount('ASSET')
        userCnt = self.dataCount('USER_INFO')
        
        for x in range( count ):            
            status = statusList[random.randrange(0,4)]             
            randDate = bulkData.randDate()            
            description = wordList[random.randrange(0,846)] + ' ' + wordList[random.randrange(0,846)]
            assetID = random.randrange(1,assetCnt+1)
            userID = random.randrange(1,userCnt+1)
            
            dic = {'status':status , 'startDate':randDate[0] , 'endDate':randDate[1] , 'finalDate':randDate[2] , 
                   'description':description , 'assetID':assetID , 'userID':userID}
            
            self.exct( """
                      INSERT INTO ASSET_JOB(status,startDate,endDate,finalDate,description,assetID,userID) 
                      VALUES('%(status)s','%(startDate)s','%(endDate)s','%(finalDate)s','%(description)s','%(assetID)s','%(userID)s')""" % dic )     
            
        print 'Complete asset job'
        
        
        
    def BULK_ASSET_JOB( self ):
        statusList = ['Ready' , 'WIP' , 'Final' , 'Hold']
        wordList = self.wordList() 
        assetCnt = self.dataCount('ASSET')
        for x in range( assetCnt ):            
            status = statusList[random.randrange(0,4)]             
            randDate = bulkData.randDate()            
            description = wordList[random.randrange(0,846)] + ' ' + wordList[random.randrange(0,846)]
            userID = 2
            dic = {'status':status , 'startDate':randDate[0] , 'endDate':randDate[1] , 'finalDate':randDate[2] , 
                   'description':description , 'assetID':x+1 , 'userID':userID}
            query = """
                    INSERT INTO ASSET_JOB(status,startDate,endDate,finalDate,description,assetID,userID) 
                    VALUES('%(status)s','%(startDate)s','%(endDate)s','%(finalDate)s','%(description)s','%(assetID)s','%(userID)s')""" % dic
            print query
            self.exct( query )   
        print 'Complete asset job'     
        
        
    
    def RENDER( self , count ):    
        wordList = self.wordList() 
        statusList = ['Ready' , 'WIP' , 'Final' , 'Hold']
        shotCnt = self.dataCount('SHOT')
        userCnt = self.dataCount('USER_INFO')                
        for x in range( count ):
            status = statusList[random.randrange(0,4)]  
            randDate = bulkData.randDate() 
            description = wordList[random.randrange(0,846)] + ' ' + wordList[random.randrange(0,846)]
            shotID = random.randrange(1,shotCnt+1)
            userID = random.randrange(1,userCnt+1)
            dic = {'status':status , 'startDate':randDate[0] , 'endDate':randDate[1] , 'finalDate':randDate[2] , 
                   'description':description , 'shotID':shotID , 'userID':userID}
            if self.pkCheck('RENDER', 'shotID', 'userID', shotID, userID) == 0:
                    self.exct( """
                      INSERT INTO RENDER(status,startDate,endDate,finalDate,description,shotID,userID) 
                      VALUES('%(status)s','%(startDate)s','%(endDate)s','%(finalDate)s','%(description)s','%(shotID)s','%(userID)s')""" % dic ) 
        
        print 'Complete render' 
        
        
        
    def COMP( self , count ):    
        wordList = self.wordList() 
        statusList = ['Ready' , 'WIP' , 'Final' , 'Hold']
        shotCnt = self.dataCount('SHOT')
        userCnt = self.dataCount('USER_INFO')                
        for x in range( count ):
            status = statusList[random.randrange(0,4)]  
            randDate = bulkData.randDate() 
            description = wordList[random.randrange(0,846)] + ' ' + wordList[random.randrange(0,846)]
            shotID = random.randrange(1,shotCnt+1)
            userID = random.randrange(1,userCnt+1)
            dic = {'status':status , 'startDate':randDate[0] , 'endDate':randDate[1] , 'finalDate':randDate[2] , 
                   'description':description , 'shotID':shotID , 'userID':userID}
            if self.pkCheck('COMP', 'shotID', 'userID', shotID, userID) == 0:
                    self.exct( """
                      INSERT INTO COMP(status,startDate,endDate,finalDate,description,shotID,userID) 
                      VALUES('%(status)s','%(startDate)s','%(endDate)s','%(finalDate)s','%(description)s','%(shotID)s','%(userID)s')""" % dic ) 
        
        print 'Complete comp' 
        
                
          
        
    def JOB_INFO( self , count ):
        wordList = self.wordList() 
        statusList = ['Ready' , 'WIP' , 'Final' , 'Hold'] 
        shotCnt = self.dataCount('SHOT')
        userCnt = self.dataCount('USER_INFO')
        deptCnt = self.dataCount('DEPARTMENT')
        for x in range( count ):
            status = statusList[random.randrange(0,4)]  
            randDate = bulkData.randDate() 
            description = wordList[random.randrange(0,846)] + ' ' + wordList[random.randrange(0,846)]
            shotID = random.randrange(1,shotCnt+1)
            userID = random.randrange(1,userCnt+1)
            deptID = random.randrange(1,deptCnt+1)
            dic = {'status':status , 'startDate':randDate[0] , 'endDate':randDate[1] , 'finalDate':randDate[2] , 
                   'description':description , 'shotID':shotID , 'userID':userID , 'deptID':deptID}
            if self.pkCheck('JOB_INFO', 'shotID', 'userID', shotID, userID) == 0:
                    self.exct( """
                      INSERT INTO JOB_INFO(status,startDate,endDate,finalDate,description,shotID,userID,deptID) 
                      VALUES('%(status)s','%(startDate)s','%(endDate)s','%(finalDate)s','%(description)s','%(shotID)s','%(userID)s','%(deptID)s')""" % dic ) 
        
        print 'Complete JOB_INFO'
    
    
                
if __name__ == '__main__':
    
    bulkData = bulkData()
#    bulkData.BULK_ASSET_JOB()
#    bulkData.basicData()

#    bulkData.insertMachine( 100 )
#    bulkData.insertUser( 140 )
#    bulkData.insertAsset( 100 )
#    bulkData.insertSeq( 50 )
#    bulkData.insertShot( 5 )
#    bulkData.SEQUENCES_has_ASSET(100)
#    bulkData.ASSET_TAG()
#    bulkData.SHOT_has_ASSET( 500 )  
#    bulkData.ASSET_VERSION()
#    bulkData.ASSET_has_ASSET_TAG()    
#    bulkData.ASSET_JOB(500)
#    bulkData.RENDER(3)
#    bulkData.COMP(30)
#    bulkData.JOB_INFO(100)

#    bulkData.RESET('ASSET_VERSION')    